/**
  * Copyright (c) CSIRO Australia, 2009
  *
  * @author $Author: jia020 $
  * @version $Id: WorkflowTask.java 643 2009-05-20 00:20:55Z jia020 $
  */
package au.csiro.nt.pdsp.workflow;

import java.io.IOException;
import java.io.StringReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import au.csiro.nt.pdsp.client.PdspConst;
import au.csiro.nt.pdsp.util.Base64Coder;
import au.csiro.nt.pdsp.util.Config;
import au.csiro.nt.pdsp.util.MetaItem;

//import java.s.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;


/**
 * WorkflowTask class will contain multiple workflows, then monitor all the
 * status of workflows.
 * 
 * @author jia020
 * 
 */
public class WorkflowTask {

	private int taskId;
	protected HashMap<String, Workflow> workflowMap = new HashMap<String, Workflow>();
	@SuppressWarnings("unchecked")
	/**
	 * Construct a list of MetaItem based a sample xml record.
	 * @param xmlRecord a string of sample xml record.
	 * @return ArrayList of MetaItems
	 */	
	private ArrayList<MetaItem<String,String>> getColumnsDefinition(String xmlRecord) {
		//sample
//		<record>
//		<centrelinkid>Wan703</centrelinkid>
//		<driverid>10871319</driverid>
//		<firstname>Chen</firstname>
//		<lastname>Wang</lastname>
//		<birthday>1949-01-31</birthday>
//		<martial>Married</martial>
//		</record>
		String XML_TAG_RECORD = "record";
		String XML_TAG_NAME = "name";
		
		ArrayList<MetaItem<String,String>> columns = new ArrayList();
		
		InputSource input = new InputSource(new StringReader(xmlRecord));
		DocumentBuilderFactory dBF = DocumentBuilderFactory.newInstance();
		dBF.setNamespaceAware(true);
		dBF.setIgnoringComments(true);

		DocumentBuilder builder = null;
		Document doc;
		try {
			builder = dBF.newDocumentBuilder();
			doc = builder.parse(input);
			//get record node
			Node node = doc.getElementsByTagName(XML_TAG_RECORD).item(0);
			//get child nodes
			NodeList nodes = node.getChildNodes();
			String name = null;
			String type = null;
			//FIXME:node index should start from 0 --- (len-1) ??
			for (int i = 0; i < nodes.getLength(); i++) {
				Node nodeChild = nodes.item(i);
				name = nodeChild.getNodeName();
				type = Config.getInstance().columnsMap.get(name);
				columns.add(new MetaItem(name,type));
			}			
		} catch (SAXException e) {			
		} catch (ParserConfigurationException e) {
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return columns;
	}	
	/**
	 * Construct XML task data records for a particular userid.
	 * @param userid.
	 * @return XML task data.
	 */		
	public String createXMLTasks(String userid) {
		String sql = "select taskid,status,logs from task where userid='"+userid+"'";
		ResultSet rs = Config.getInstance().mysql.execSelect(sql);
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<pdsp>");
		sb.append("<metadata>");	
		sb.append("<columnsdefinition>");
		sb.append("<column name=\"taskid\" type=\"integer\" />");
		sb.append("<column name=\"status\" type=\"string\" />");
		sb.append("<column name=\"logs\" type=\"string\" />");	
		sb.append("</columnsdefinition>");
		sb.append("</metadata>");
		try {
			sb.append("<records>");			
			while (rs!=null && rs.next()) {
				sb.append("<record>");
					sb.append("<taskid>");sb.append(rs.getInt("taskid"));sb.append("</taskid>");						
					sb.append("<status>");sb.append(rs.getString("status"));sb.append("</status>");			
					sb.append("<logs>");sb.append(rs.getString("logs"));sb.append("</logs>");								
				sb.append("</record>");			
			}
			sb.append("</records>");		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sb.append("</pdsp>");		
		return sb.toString();
	}
	/**
	 * Construct XML task data records in tasks table.
	 * @return XML task data.
	 */		
	public String createXMLAllWorkflows() {
		String sql = "select workflowid,originaldataset,logs from workflow";
		ResultSet rs = Config.getInstance().mysql.execSelect(sql);
		String xmlRecord = null;
		StringBuffer sbRecords = new StringBuffer();
		StringBuffer sbRecord = new StringBuffer();
		String tag = "<record>";
		int index=0;
		int workflowid=0;
		String logs = "";
		try {
			sbRecords.append("<records>");			
			while (rs!=null && rs.next()) {
				//insert <workflowid> 8 </workflowid> into originaldataset;
				workflowid = rs.getInt("workflowid");
				logs = rs.getString("logs");
				sbRecord.append(rs.getString("originaldataset"));
				//add workflowid
				index = sbRecord.indexOf(tag)+tag.length();			
				sbRecord.insert(index, "<workflowid>"+workflowid+"</workflowid>");
				//add logs
				index = sbRecord.indexOf(tag)+tag.length();		
				sbRecord.insert(index, "<logs>"+logs+"</logs>");	
				//append record to records
				sbRecords.append(sbRecord.toString());
				//reset sbRecord
				xmlRecord = sbRecord.toString();
				sbRecord.setLength(0);
			}
			sbRecords.append("</records>");		
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<pdsp>");
		sb.append("<metadata>");	
		sb.append("<columnsdefinition>");
		String name,type;
		ArrayList<MetaItem<String, String>> columns = getColumnsDefinition(xmlRecord); 
		for (int i=0;i<columns.size();i++) {
			//sample: <column name="centrelinkid" type="string" />
			MetaItem metaItem = columns.get(i);
			name = (String) metaItem.getFirst();
			type = (String) metaItem.getSecond();			
			sb.append("<column name=\"");sb.append(name);sb.append("\" type=\"");sb.append(type);sb.append("\" />");			
		}		
		sb.append("</columnsdefinition>");
		sb.append("</metadata>");
		sb.append(sbRecords);		
		sb.append("</pdsp>");		
		return sb.toString();
	}	
	/**
	 * Construct XML workflow data records for unreviewed workflows.
	 * @return XML workflow data.
	 */		
	public String createXMLUnreviewedWorkflows() {
		String sql = "select workflowid,originaldataset from workflow where status='" + PdspConst.WF_STATUS_INIT + "'";
		ResultSet rs = Config.getInstance().mysql.execSelect(sql);
		String xmlRecord = null;
		StringBuffer sbRecords = new StringBuffer();
		StringBuffer sbRecord = new StringBuffer();
		String tag = "<record>";
		int index=0;
		int workflowid=0;
		try {
			sbRecords.append("<records>");			
			while (rs!=null && rs.next()) {
				//insert <workflowid> 8 </workflowid> into originaldataset;
				workflowid = rs.getInt("workflowid");
				sbRecord.append(rs.getString("originaldataset"));
				index = sbRecord.indexOf(tag)+tag.length();			
				sbRecord.insert(index, "<workflowid>"+workflowid+"</workflowid>");
				sbRecords.append(sbRecord.toString());
				xmlRecord = sbRecord.toString();
				sbRecord.setLength(0);
			}
			sbRecords.append("</records>");		
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<pdsp>");
		sb.append("<metadata>");	
		sb.append("<columnsdefinition>");
		String name,type;
		ArrayList<MetaItem<String, String>> columns = getColumnsDefinition(xmlRecord); 
		for (int i=0;i<columns.size();i++) {
			//sample: <column name="centrelinkid" type="string" />
			MetaItem metaItem = columns.get(i);
			name = (String) metaItem.getFirst();
			type = (String) metaItem.getSecond();			
			sb.append("<column name=\"");sb.append(name);sb.append("\" type=\"");sb.append(type);sb.append("\" />");			
		}		
		sb.append("</columnsdefinition>");
		sb.append("</metadata>");
		sb.append(sbRecords);		
		sb.append("</pdsp>");		
		return sb.toString();
	}
	/**
	 * Construct XML workflow data records relating to a particular taskid.
	 * @param taskid.
	 * @return XML workflow data.
	 */		
	public String createXMLWorkflows(int taskid) {
		String sql = "select filtereddataset from workflow where taskid="+taskid + " and status='" + PdspConst.WF_STATUS_AUTHORIZED + "'";
		ResultSet rs = Config.getInstance().mysql.execSelect(sql);
		String xmlRecord = null;
		StringBuffer sbRecords = new StringBuffer();
		try {
			sbRecords.append("<records>");			
		while (rs!=null && rs.next()) {
			sbRecords.append(rs.getString("filtereddataset"));
			xmlRecord = rs.getString("filtereddataset");
		}
		sbRecords.append("</records>");		
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<pdsp>");
		sb.append("<metadata>");	
		sb.append("<columnsdefinition>");
		String name,type;
		ArrayList<MetaItem<String, String>> columns = getColumnsDefinition(xmlRecord); 
		for (int i=0;i<columns.size();i++) {
			//sample: <column name="centrelinkid" type="string" />
			MetaItem metaItem = columns.get(i);
			name = (String) metaItem.getFirst();
			type = (String) metaItem.getSecond();			
			sb.append("<column name=\"");sb.append(name);sb.append("\" type=\"");sb.append(type);sb.append("\" />");			
		}		
		sb.append("</columnsdefinition>");
		sb.append("</metadata>");
		sb.append(sbRecords);		
		sb.append("</pdsp>");		
		return sb.toString();
	}
	/**
	 * Construct XML workflow data records for sample workflows.
	 * @param originalQuery SQL string for original query.
	 * @param sampleQuery SQL string for sample query.
	 * @return XML workflow data.
	 */		
	public String createXMLWorkflowsSample(String originalQuery,String sampleQuery) {
		//input:sampleQuery:select centrelinkid, firstname,lastname,healthy,income from privacydata where centrelinkid='sample'
		Map recordMap = createRecord(sampleQuery);
		String xmlRecord = (String) recordMap.get("Sample");
		
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<pdsp>");
		sb.append("<metadata>");	
		sb.append("<query>");		
		String queryBase64 = Base64Coder.encodeString(originalQuery);
		sb.append(queryBase64);
		sb.append("</query>");			
		sb.append("<columnsdefinition>");
		String name,type;
		ArrayList<MetaItem<String, String>> columns = getColumnsDefinition(xmlRecord); 
		for (int i=0;i<columns.size();i++) {
			//sample: <column name="centrelinkid" type="string" />
			MetaItem metaItem = columns.get(i);
			name = (String) metaItem.getFirst();
			type = (String) metaItem.getSecond();			
			sb.append("<column name=\"");sb.append(name);sb.append("\" type=\"");sb.append(type);sb.append("\" />");			
		}		
		sb.append("</columnsdefinition>");
		sb.append("</metadata>");
		for (int i=0;i<PdspConst.WF_SAMPLE_NUMBER;i++)
			sb.append(xmlRecord);		
		sb.append("</pdsp>");		
		return sb.toString();		
	}
	@SuppressWarnings("unchecked")
	/**
	 * Construct XML record data(xmlRecord) for select query.
	 * Create a Map<centrelinkid,xmlRecord>.
	 * Return the Map to the caller.
	 * @param sql SQL string for select query.
	 * @return Map<centrelinkid,xmlRecord>.
	 */		
	public Map createRecord(String sql) {
		//sample data for recordMap
		//key:Wan703
		//value:<record>	<centrelinkid>Wan703</centrelinkid>	<driverid>10871319</driverid>	<firstname>Chen</firstname>	<lastname>Wang</lastname>	</record>
		StringBuffer sb = new StringBuffer();
		String name,type;
		String centrelinkid = null;
		Map recordMap = new HashMap<String,String>();
		DateFormat df = new SimpleDateFormat("MM-dd-yyyy");	

		try {
		ResultSet rs = Config.getInstance().mysql.execSelect(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
			while (rs!=null && rs.next()) {
				sb.delete(0, sb.length());
				sb.append("<record>");
				for (int i=1;i<=columnCount;i++) {
					name = rsmd.getColumnName(i);	
					type = rsmd.getColumnTypeName(i);//FIXME:
					type = Config.getInstance().columnsMap.get(name);				
					sb.append("<");sb.append(name);sb.append(">");
					
					if (type.equalsIgnoreCase("string"))
						sb.append(rs.getString(name));
					else if (type.equalsIgnoreCase("float"))
						sb.append(rs.getFloat(name));
					else if (type.equalsIgnoreCase("integer"))
						sb.append(rs.getInt(name));		
					else if (type.equalsIgnoreCase("date")) {	
						sb.append(df.format(rs.getDate(name)));		
						}
					sb.append("</");sb.append(name);sb.append(">");						
				}				
				sb.append("</record>");			
				
				centrelinkid = rs.getString("centrelinkid");
				recordMap.put(centrelinkid, sb.toString());
		} 
		}catch(Exception ex) {
			ex.printStackTrace();
			
		}
		return recordMap;		
	}
	/**
	 * Review a workflow.
	 * @param workflowid .
	 * @return Map<centrelinkid,xmlRecord>.
	 */		
	public boolean reviewWorkflow(int workflowid) {
		// TODO Auto-generated method stub
//		String sql = "update workflow set status='" + PdspConst.WF_STATUS_REVIEWED + "' where workflowid=" + workflowid;
//		boolean bOK = Config.getInstance().mysql2.execUpdate(sql);		
		Config.getInstance().pdspLogger.logWorkflow(0, workflowid, PdspConst.WF_STATUS_REVIEWED,PdspConst.WF_STATUS_REVIEWED + ":admin");	
		return true;
	}
	/**
	 * Create a new task.
	 * @param userid String of creater .
	 * @param query String of SQL query.
	 * @param description description of the task.
	 * @return taskid if the new task was successfully created. Otherwise return 0.
	 */		
	public int newTask(String userid, String query,String description) throws WorkflowException,SQLException {

		this.taskId = 0;
		ResultSet rs = null;
		java.sql.PreparedStatement ps = null;
		try {
			//insert into task
			ps = Config.getInstance().mysql2.getConnection().prepareStatement(
					"insert into task(userid,query,description) values(?,?,?)");
			ps.setString(1, userid);
			ps.setString(2, query);
			ps.setString(3, description);			
			boolean bOK = true;// Fixme:ps.execute() always return false????
			ps.execute();
			if (!bOK)
				return 0;
			//create originaldataset map for workflows.
			Map recordMap = createRecord(query);
			
			//get current new-added taskid
			String sql = "select max(taskid) taskid from task";
			rs = Config.getInstance().mysql.execSelect(sql);
			if (rs != null && rs.next()) {
				this.taskId = rs.getInt("taskid");
				rs.close();
				//Set task status to create
				Config.getInstance().pdspLogger.logTask(0, this.taskId, PdspConst.TASK_STATUS_CREATE,PdspConst.TASK_STATUS_CREATE + ":" + userid);		
				//create workflows for task
				String individualuserid, organisationuserid, wf_status, originalquery, originaldataset, signature;
				String user_status = "";
				Date createtime;
				Date endtime;
				Calendar calendar = Calendar.getInstance();
				
		    Set<Map.Entry<String, String>> set = recordMap.entrySet();

		    for (Map.Entry<String, String> me : set) {
					createtime = calendar.getTime();
					calendar.add(Calendar.DATE,5);
					endtime = calendar.getTime();		    	
					individualuserid = me.getKey();
		      originaldataset= me.getValue();
					organisationuserid = userid;				
					originalquery = query;
					signature="signature of " + userid;
					
					sql = "select status from users where username = '" + individualuserid + "'";
					rs = Config.getInstance().mysql.execSelect(sql);
					if (rs != null && rs.next()) {
						user_status = rs.getString("status");
						rs.close();
						rs = null;
					}
					if (!user_status.equalsIgnoreCase(PdspConst.USER_STATUS_SUBSCRIBED)) {
						continue;
					}
						
					sql = "insert into workflow(taskid,individualuserid,organisationuserid,createtime,endtime,"
							+ "originalquery,originaldataset,signature) values (?,?,?,?,?,?,?,?)";
					ps.close();
					ps = Config.getInstance().mysql2.getConnection().prepareStatement(sql);
					ps.setInt(1, this.taskId);
					ps.setString(2, individualuserid);	
					ps.setString(3, organisationuserid);	
					ps.setDate(4, new java.sql.Date(createtime.getTime()));	
					ps.setDate(5, new java.sql.Date(endtime.getTime()));	
					ps.setString(6, originalquery);	
					ps.setString(7, originaldataset);	
					ps.setString(8, signature);		
					ps.execute();

					int workflowid = 0;
					//get current new-added workflowid
					sql = "select max(workflowid) workflowid from workflow";
					rs = Config.getInstance().mysql.execSelect(sql);
					if (rs != null && rs.next()) {
						workflowid = rs.getInt("workflowid");
						rs.close();			
					} else {
						System.out.println("WorkflowTask:Couldn't retrieve the workflowid");
					}
						//Set workflow status to init
					if (workflowid>0)
						Config.getInstance().pdspLogger.logWorkflow(0, workflowid, PdspConst.WF_STATUS_INIT,PdspConst.WF_STATUS_INIT + ":" + userid);		

				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		 finally {
			 if (rs != null)
				 rs.close();
			 if (ps != null)
				 ps.close();
		 }
		return taskId;
	}

	/**
	 * Get size of WorkflowTask.
	 * 
	 * @return size
	 */
	public int getSize() {
		return workflowMap.size();
	}

	/**
	 * Get a workflow by workflowID from WorkflowTask.
	 * 
	 * @return the workflow
	 */
	public Workflow getWorkflow(String workflowID) {
		return workflowMap.get(workflowID);
	}

	/**
	 * Add a workflow into WorkflowTask.
	 * 
	 * @param workflow
	 *          the workflow to set
	 */
	public void addWorkflow(Workflow workflow) {
		workflowMap.put(workflow.getWorkflowID(), workflow);
	}

	/**
	 * Remove a workflow by workflowID from WorkflowTask.
	 * 
	 * @param workflowID
	 *          the workflow
	 */
	public void removeWorkflow(String workflowID) {
		workflowMap.remove(workflowID);
	}
	/**
	 * Construct XML data for workflows.
	 * A dispatch function based on the type
	 * @param type 
	 * @param param
	 * @return XML workflow data.
	 */		
	public String createXMLWorkflows(int type, int param) {
		String result = "";
		if (type == PdspConst.WF_GET_ALL) {
			result = createXMLAllWorkflows();
		} else if (type == PdspConst.WF_GET_BYTASKID) {
			createXMLWorkflows(param);
		} else if (type == PdspConst.WF_GET_UNREVIEWED) {
			
		}
		return result;
	}

}
